import os
from datetime import datetime, timedelta
from flask import (
    render_template,
    request,
    flash,
    redirect,
    url_for,
    jsonify,
    g,
    send_from_directory,
    Blueprint,
)
from models import app, User, Jobnumber, Category, Subcategory, Task, db
from sqlalchemy.sql import func, extract
from flask_login import (
    LoginManager,
    login_required,
    login_user,
    logout_user,
    current_user,
)
from flask_wtf import FlaskForm
from functools import wraps
import plotly
import plotly.graph_objs as go
import json
import pandas as pd
import numpy as np
from itertools import groupby
from operator import itemgetter
from models import db
from collections import namedtuple

bp_plot = Blueprint("bp_plot", __name__)


# 神奇的装饰器
def admin_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not current_user.admin:
            flash("权限不足!", category="danger")
            return redirect(url_for("login"), code=302)
        return f(*args, **kwargs)

    return decorated


# 获取海工设计总工时 按月
def get_jmo_total_manhour(year, month):
    sql = """
    select sum(task.manhour) as sum
    from task
    where task.userid != 1 and task.categoryid != 33 and task.delete = 0
    and year(task.tasktime) = {} and month(task.tasktime) = {}
    """.format(
        year, month
    )
    result = db.session.execute(sql).fetchall()
    return result[0][0]


# 获取用户设计总工时 按月
def get_user_total_manhour(year, month, userid):
    sql = """
    select sum(task.manhour) as sum
    from task
    where task.userid = {2} and task.categoryid != 33 and task.delete = 0
    and year(task.tasktime) = {0} and month(task.tasktime) = {1}
    """.format(
        year, month, userid
    )
    result = db.session.execute(sql).fetchall()
    return result[0][0]


# 获取用户当月休假情况
def get_user_vacation_manhour(year, month, userid):
    sql = """
    select subcategory.name, sum(task.manhour) as sum
    from task
    left join subcategory
    on task.subcategoryid = subcategory.id
    where task.userid = {2} and task.categoryid = 33 and task.delete = 0
    and year(task.tasktime) = {0} and month(task.tasktime) = {1}
    group by subcategory.id
    """.format(
        year, month, userid
    )
    result = db.session.execute(sql).fetchall()
    return result


# 获取海工设计工时数据 按天 项目分类汇总
def get_jmo_project_timesheet():
    sql = """
    select jobnumber.name as 'jobNo', jobnumber.desc as 'jobName', date(task.tasktime) as field, sum(task.manhour) as sum
    from task
    left join jobnumber
    on task.jobnumberid = jobnumber.id
    where task.userid != 1 and task.categoryid != 33 and task.delete = 0
    group by jobnumberid,field
    """

    result = db.session.execute(sql).fetchall()
    out = []
    for k, v in groupby(sorted(result, key=itemgetter(0, 2)), key=itemgetter(0)):
        v = list(v)
        sums = [x[3] for x in v]
        tmes = [x[2] for x in v]
        out.append([k, sums, tmes])
    return out


# 获取海工设计工时数据 按月 项目 分类汇总
def get_jmo_project_timesheet_by_month(year, month):
    sql = """
    select jobnumber.name as 'jobNo', sum(task.manhour) as sum
    from task
    left join jobnumber
    on task.jobnumberid = jobnumber.id
    where task.userid != 1 and task.categoryid != 33 and task.delete = 0
    and year(task.tasktime) = {} and month(task.tasktime) = {}
    group by jobnumberid
    """.format(
        year, month
    )

    result = db.session.execute(sql).fetchall()
    labels = []
    values = []
    for r in result:
        labels.append(r[0])
        values.append(r[1])
    trace = go.Pie(labels=labels, values=values, title="按项目")
    graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


# 获取海工设计工时数据 按月 大类 分类汇总
def get_jmo_category_timesheet_by_month(year, month):
    sql = """
    select category.name as 'category', sum(task.manhour) as sum
    from task
    left join category
    on task.categoryid = category.id
    where task.userid != 1 and task.categoryid != 33 and task.delete = 0
    and year(task.tasktime) = {} and month(task.tasktime) = {}
    group by category
    """.format(
        year, month
    )

    result = db.session.execute(sql).fetchall()
    labels = []
    values = []
    for r in result:
        labels.append(r[0])
        values.append(r[1])
    trace = go.Pie(labels=labels, values=values, title="按大类")
    graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


# 获取某项目设计工时数据 大类 汇总
def get_category_timesheet_by_project(project_id):
    sql = """
    select category.name as 'category', sum(task.manhour) as sum
    from task
    left join category
    on task.categoryid = category.id
    where task.delete = 0 and task.jobnumberid = {}
    group by category
    """.format(
        project_id
    )

    result = db.session.execute(sql).fetchall()
    labels = []
    values = []
    for r in result:
        labels.append(r[0])
        values.append(r[1])
    trace = go.Pie(labels=labels, values=values, title="按大类")
    graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


# 获取某项目设计工时数据 按员工 汇总
def get_user_timesheet_by_project(project_id):
    sql = """
    select user.name as 'category', sum(task.manhour) as sum
    from task
    left join user
    on task.userid = user.id
    where task.delete = 0 and task.jobnumberid = {}
    group by category
    """.format(
        project_id
    )

    result = db.session.execute(sql).fetchall()
    labels = []
    values = []
    for r in result:
        labels.append(r[0])
        values.append(r[1])
    trace = go.Pie(labels=labels, values=values, title="按用户")
    graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


# 获取海工设计工时数据 按月 分类 分类汇总
def get_jmo_subcategory_timesheet_by_month(year, month):
    sql = """
    select subcategory.name as 'subcategory', sum(task.manhour) as sum
    from task
    left join subcategory
    on task.subcategoryid = subcategory.id
    where task.userid != 1 and task.categoryid != 33 and task.delete = 0
    and year(task.tasktime) = {} and month(task.tasktime) = {}
    group by subcategory
    """.format(
        year, month
    )

    result = db.session.execute(sql).fetchall()
    labels = []
    values = []
    for r in result:
        labels.append(r[0])
        values.append(r[1])
    trace = go.Pie(labels=labels, values=values, title="按分类")
    graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


# 获取用户按周工时统计数据
def get_user_timesheet_by_week(year, userid):
    sql = """
    select jobnumber.name as 'jobNo', jobnumber.desc as 'jobName', week(task.tasktime) as field, sum(task.manhour) as sum
    from task
    left join jobnumber
    on task.jobnumberid = jobnumber.id
    where year(task.tasktime) = {} and task.userid = {} and task.categoryid != 33 and task.delete = 0
    group by jobnumberid,field
    """.format(
        year, userid
    )

    result = db.session.execute(sql)
    results = result.fetchall()
    if not results:
        return ""

    # 由于有些用户没有填写前几周的工时，导致周显示错误
    # 尝试在数组中混入每周为0 的数据
    i = 0
    for r in results:
        week = r[2]
        if i < week:
            i = week
    jobnumber = results[0][0]
    jobname = results[0][1]
    for j in range(i + 1):
        results.append((jobnumber, jobname, j, 0))

    df = pd.DataFrame(results)
    df.columns = result.keys()
    df = pd.pivot_table(
        df,
        index=["jobNo", "jobName"],
        columns=["field"],
        values=["sum"],
        aggfunc=[np.sum],
        fill_value=0,
    )
    data = []
    rows = list(df.itertuples())
    if rows:
        n = list(rows[0]).__len__()
        data.append(["工作令号", "项目名称"] + ["{}周".format(x) for x in range(1, n)])
        for row in rows:
            row = list(row)
            data.append(list(row[0]) + row[1:])
        return data
    else:
        return ""


# 按月获取iMES工时统计数据
def get_imes_timesheet_by_month(year, month):
    sql = """
    select user.position as 'userPos', jobnumber.name as 'jobNo', user.name as 'userName', user.no as 'userNo', sum(task.manhour) as sum
    from task
    left join jobnumber
    on task.jobnumberid = jobnumber.id
    left join user
    on task.userid = user.id
    where year(task.tasktime) = {} and month(task.tasktime) = {}
    and task.userid != 1 and task.categoryid != 33 and task.delete = 0
    group by jobnumberid,userNo
    order by user.position,userNo
    """.format(
        year, month
    )

    result = db.session.execute(sql)
    results = result.fetchall()
    if not results:
        return ""
    df = pd.DataFrame(results)
    df.columns = result.keys()
    df = pd.pivot_table(
        df,
        index=["userPos", "userNo", "userName"],
        columns=["jobNo"],
        values=["sum"],
        aggfunc=[np.sum],
        fill_value=0,
    )

    data = []
    rows = list(df.itertuples())
    if rows:
        data.append(
            ["工号", "姓名"]
            + [
                "{}工时".format(x[2].replace("JMH-ZC-00000", "非项目"))
                for x in list(df.columns.values)
            ]
        )
        for row in rows:
            row = list(row)
            data.append(list(row[0])[1:] + row[1:])
        return data
    else:
        return ""


# 获取用户按月工时统计数据
def get_user_timesheet_by_month(year, month, userid):
    sql = """
    select jobnumber.name as 'jobNo', jobnumber.desc as 'jobDesc', sum(task.manhour) as sum,GROUP_CONCAT(task.title SEPARATOR ';')
    from task
    left join jobnumber
    on task.jobnumberid = jobnumber.id
    where year(task.tasktime) = {} and month(task.tasktime) = {}
    and task.userid = {} and task.categoryid != 33 and task.delete = 0
    group by jobnumberid
    """.format(
        year, month, userid
    )

    result = db.session.execute(sql)
    results = result.fetchall()
    if not results:
        return ""
    else:
        labels = []
        values = []
        for r in results:
            labels.append(r[0])
            values.append(r[2])
        trace = go.Pie(labels=labels, values=values)
        graphJSON = json.dumps(trace, cls=plotly.utils.PlotlyJSONEncoder)
        return (results, graphJSON)


# 绘制海工设计工时分布图
def create_jmo_project_timesheet_plot():
    data = []
    res = get_jmo_project_timesheet()
    for k, sums, tmes in res:
        data.append({"x": tmes, "y": sums, "type": "line", "name": k})

    # style all the traces
    for k in range(len(data)):
        data[k].update(
            {
                "line": {"width": 0.5},
                "marker": {"size": 8},
                "mode": "lines+markers",
                "showlegend": True,
            }
        )
    layout = {
        "title": "海工设计工时分布图",
        "xaxis": {
            "autorange": True,
            "rangeslider": {"autorange": True,},
            "title": "日期",
        },
        "yaxis": {"title": "工时"},
    }
    fig = dict(data=data, layout=layout)
    graphJSON = json.dumps(fig, cls=plotly.utils.PlotlyJSONEncoder)
    return graphJSON


@bp_plot.route("/", methods=["GET"])
@login_required
def index():
    userid = current_user.id
    try:
        month = int(request.args.get("month"))
        year = int(request.args.get("year"))
        jmoall_plot = create_jmo_project_timesheet_plot()
        return render_template(
            "plot/index.html",
            jmoall=jmoall_plot,
            jmomonth=get_jmo_project_timesheet_by_month(year, month),
            jmomonthcategory=get_jmo_category_timesheet_by_month(year, month),
            jmomonthsubcategory=get_jmo_subcategory_timesheet_by_month(year, month),
            timesheet_by_week=get_user_timesheet_by_week(year, userid),
            timesheet_by_month=get_user_timesheet_by_month(year, month, userid),
            jmomanhour_month=get_jmo_total_manhour(year, month),
            usermanhour_month=get_user_total_manhour(year, month, userid),
            uservacation=get_user_vacation_manhour(year, month, userid),
            imesdata=get_imes_timesheet_by_month(year, month),
            year=year,
            month=month,
        )
    except Exception as e:
        flash("What are you doing?", category="danger")
        return redirect(url_for("bp_user.index"), code=302)


@bp_plot.route("/dept", methods=["GET"])
@login_required
def dept():
    try:
        month = int(request.args.get("month"))
        year = int(request.args.get("year"))
        jmoall_plot = create_jmo_project_timesheet_plot()
        return render_template(
            "plot/index.html",
            jmoall=jmoall_plot,
            jmomonth=get_jmo_project_timesheet_by_month(year, month),
            jmomonthcategory=get_jmo_category_timesheet_by_month(year, month),
            jmomonthsubcategory=get_jmo_subcategory_timesheet_by_month(year, month),
            jmomanhour_month=get_jmo_total_manhour(year, month),
            imesdata=get_imes_timesheet_by_month(year, month),
            year=year,
            month=month,
        )
    except Exception as e:
        print(e)
        flash("What are you doing?", category="danger")
        return redirect(url_for("bp_user.index"), code=302)


@bp_plot.route("/user", methods=["GET"])
@login_required
def user():
    userid = request.args.get("userid")
    if userid:
        userid = int(userid)
    else:
        userid = current_user.id
    user_name = db.session.query(User.name).filter(User.id == userid).all()[0][0]
    try:
        month = int(request.args.get("month"))
        year = int(request.args.get("year"))
        return render_template(
            "plot/index.html",
            timesheet_by_week=get_user_timesheet_by_week(year, userid),
            timesheet_by_month=get_user_timesheet_by_month(year, month, userid),
            usermanhour_month=get_user_total_manhour(year, month, userid),
            uservacation=get_user_vacation_manhour(year, month, userid),
            year=year,
            month=month,
            user_name=user_name,
        )
    except Exception as e:
        print(e)
        flash("What are you doing?", category="danger")
        return redirect(url_for("bp_user.index"), code=302)


if __name__ == "__main__":

    pass
